package jp.co.mclnet.util;

import java.util.Calendar;
import java.util.Date;

import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;

public class CalcUtils {

    private static final int DAYS_OF_YEAR = 365;
    private static final String INTEGER_FORMAT = "^[0-9]+[.][0]";
    private static final String PIRIOD = "\\.";
    private static final int START_YEAR = 1900;

    private static Date convertSerialValue(int serialValue) {
        int day = serialValue;
        if (60 <= day) {
            day--;
        }
        int leapYearCount = 0;
        for (int i = 0; i < day / DAYS_OF_YEAR; i++) {
            if (isLeapYear(START_YEAR + i)) {
                leapYearCount++;
            }
        }
        day -= leapYearCount;
        int year = START_YEAR + (day / DAYS_OF_YEAR);
        day %= DAYS_OF_YEAR;
        int[] lastDays = { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
        if (isLeapYear(year)) {
            lastDays[1]++;
        }
        int month;
        for (month = 0; day >= lastDays[month]; month++) {
            day -= lastDays[month];
        }
        Calendar calendar = Calendar.getInstance();
        calendar.set(year, month, day, 0, 0, 0);
        return calendar.getTime();
    }

    public static Object getValue(Cell cell) {
        CellValue cellValue = cell.getSheet().getWorkbook().getCreationHelper()
                .createFormulaEvaluator().evaluate(cell);

        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                int numberValue = (int) cellValue.getNumberValue();
                return convertSerialValue(numberValue);
            }
            double num = cellValue.getNumberValue();
            String numStr = String.valueOf(num);
            if (numStr.matches(INTEGER_FORMAT)) {
                return Integer.valueOf(numStr.split(PIRIOD)[0]);
            } else {
                return Double.valueOf(numStr);
            }
        case Cell.CELL_TYPE_STRING:
            return cellValue.getStringValue();
        case Cell.CELL_TYPE_BLANK:
            return null;
        case Cell.CELL_TYPE_BOOLEAN:
            return new Boolean(cellValue.getBooleanValue());
        case Cell.CELL_TYPE_ERROR:
            return ErrorEval.getText(cellValue.getErrorValue());
        }
        return null;
    }

    private static boolean isLeapYear(int year) {
        return ((year % 4 == 0) && (year % 100 != 0 || year % 400 == 0));
    }

    private CalcUtils() {
        throw new UnsupportedOperationException();
    }
}
